package hms.database;

import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;

import java.sql.*;

/**
 * HSQLDB_TEST2 is a test program
 */
public class HSQLDB_TEST2 {

	public static void createTables(DAO db) {
		String createSuppliers = "CREATE TABLE SUPPLIER(SUPPLIER_ID INTEGER NOT NULL PRIMARY KEY,SUPPLIER_NAME VARCHAR(75) NOT NULL,CONSTRAINT UNQ_SUPPLIER_NAME UNIQUE(SUPPLIER_NAME))";
		String createCoffee = "CREATE TABLE COFFEE(COFFEE_ID INTEGER NOT NULL PRIMARY KEY,COFFEE_NAME VARCHAR(25) NOT NULL,SUPPLIER_ID INTEGER NOT NULL,CONSTRAINT UNQ_COFFEE_NAME UNIQUE(COFFEE_NAME),CONSTRAINT FK_SUPPLIER_COFFEE FOREIGN KEY(SUPPLIER_ID) REFERENCES SUPPLIER(SUPPLIER_ID))";
		String createLocation = "CREATE TABLE LOCATION(LOCATION_ID INTEGER NOT NULL PRIMARY KEY,LOCATION_NAME VARCHAR(75) NOT NULL,CONSTRAINT UNQ_LOCATION_NAME UNIQUE(LOCATION_NAME))";
		String createSupplierLocation = "CREATE TABLE LOCATION_SUPPLIER(LOCATION_ID INTEGER NOT NULL,SUPPLIER_ID INTEGER NOT NULL,ESTABLISHED_DATE DATE NOT NULL,CONSTRAINT SYS_PK_LOCATION_SUPPLIER PRIMARY KEY(SUPPLIER_ID,LOCATION_ID),CONSTRAINT FK_LOCATION_LOC_SUPP FOREIGN KEY(LOCATION_ID) REFERENCES LOCATION(LOCATION_ID),CONSTRAINT FK_SUPPLIER_LOC_SUPP FOREIGN KEY(SUPPLIER_ID) REFERENCES SUPPLIER(SUPPLIER_ID))";

		System.out.println(db.updateCommand(createSuppliers));
		System.out.println(db.updateCommand(createCoffee));
		System.out.println(db.updateCommand(createLocation));
		System.out.println(db.updateCommand(createSupplierLocation));
	}

	public static void dropTables(DAO db) {
		db.updateCommand("DROP TABLE LOCATION_SUPPLIER");
		db.updateCommand("DROP TABLE COFFEE");
		db.updateCommand("DROP TABLE SUPPLIER");
		db.updateCommand("DROP TABLE LOCATION");
	}

	public static void printColumns(ResultSet rs) {
		ResultSetMetaData resMetaData;
		try {
			resMetaData = rs.getMetaData();
			int colCount = resMetaData.getColumnCount();
			for (int i = 1; i <= colCount; i++) {
				System.out.print(resMetaData.getColumnName(colCount) + "\t");
			}
			System.out.println();
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public static void printResultSupplier(ResultSet rs) throws SQLException {
		try {
			printColumns(rs);
			while (rs.next()) {
				System.out.println(rs.getObject("SUPPLIER_ID") + "\t"
						+ rs.getObject("SUPPLIER_NAME"));
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private static void printResultCoffee(ResultSet rs) throws SQLException {
		try {
			printColumns(rs);
			while (rs.next()) {
				System.out.println(rs.getObject("COFFEE_ID") + "\t"
						+ rs.getObject("COFFEE_NAME") + "\t"
						+ rs.getObject("SUPPLIER_ID"));
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	private static void printResultCoffeeView(ResultSet rs) throws SQLException {
		try {
			printColumns(rs);
			while (rs.next()) {
				System.out.println(rs.getObject("SUPPLIER_ID") + "\t"
						+ rs.getObject("SUPPLIER_NAME") + "\t"
						+ rs.getObject("COFFEE_ID") + "\t"
						+ rs.getObject("COFFEE_NAME") + "\t"
						+ rs.getObject("SUPPLIER_ID"));
			}
		} catch (Exception e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public HSQLDB_TEST2() {
	}

	public static void main(String[] args) {
		try {
			String dbName = "sample_database";
			String file_location = "c:\\sample_db\\"; // location of physical
														// database files, UNIX
														// and Windows file
														// separator acceptable

			// String file_location =""; // location relative of java program

			DAO db1 = null;
			db1 = new DAO(dbName, file_location, false); // not checking if
															// dbName exists
															// already

			System.out.print("Check the filesystem at " + file_location
					+ " for the following files:\n" + "\t\t" + dbName
					+ ".properties\n" + "\t\t" + dbName + ".script");

			System.out.println("");

			DAO db2 = null;
			db2 = new DAO(dbName, file_location, false); // not checking if
															// dbName exists
															// already

			boolean success = db1.open();
			System.out.println("Database open with: " + success);
			dropTables(db1); // only run if want to drop tables
			createTables(db1); // only run once

			System.out
					.println(db1
							.updateCommand("INSERT INTO supplier(SUPPLIER_ID,SUPPLIER_NAME) VALUES(15, 'ELTON JOHN')"));
			System.out
					.println(db1
							.updateCommand("INSERT INTO supplier(SUPPLIER_ID,SUPPLIER_NAME) VALUES(20, 'PET SHOP BOYS')"));
			ResultSet rs = db1.getResult("SELECT * FROM supplier");
			printResultSupplier(rs);

			System.out
					.println(db1
							.updateCommand("INSERT INTO supplier(SUPPLIER_ID,SUPPLIER_NAME) VALUES(25, 'WHAM')"));
			System.out
					.println(db1
							.updateCommand("INSERT INTO supplier(SUPPLIER_ID,SUPPLIER_NAME) VALUES(30, 'BLONDIE')"));

			db2.open();
			ResultSet rs1 = db2.getResult("SELECT * FROM supplier");
			printResultSupplier(rs1);

			System.out
					.println(db2
							.updateCommand("INSERT INTO COFFEE(COFFEE_ID, COFFEE_NAME, SUPPLIER_ID) VALUES"
									+ "(13, 'Dark', 15),"
									+ "(23, 'Light', 20),"
									+ "(33, 'Medium', 25),"
									+ "(43, 'Apple', 30),"
									+ "(53, 'Orange', 15),"
									+ "(63, 'Banana', 20),"
									+ "(73, 'Mocha', 25),"
									+ "(83, 'Strong', 30),"
									+ "(93, 'Decaf', 15),"
									+ "(103, 'Folgers', 20)"));

			ResultSet rs2 = db1.getResult("SELECT * FROM COFFEE");
			printResultCoffee(rs2);
			db2.close();
			db1.open();  // Have to reopen connection a sticking point
			ResultSet rs3 = db1
					.getResult("SELECT SUPPLIER.SUPPLIER_ID, SUPPLIER.SUPPLIER_NAME, COFFEE.COFFEE_ID, COFFEE.COFFEE_NAME FROM COFFEE INNER JOIN SUPPLIER ON COFFEE.SUPPLIER_ID=SUPPLIER.SUPPLIER_ID");
			printResultCoffeeView(rs3);

			db1.close();

			System.out.println(db1.open());

			System.out
					.println(db1
							.updateCommand("INSERT INTO supplier(SUPPLIER_ID,SUPPLIER_NAME) VALUES(115, 'p Styx')"));
			System.out
					.println(db1
							.updateCommand("INSERT INTO supplier(SUPPLIER_ID,SUPPLIER_NAME) VALUES(120, 'p Chciago')"));
			ResultSet rs4 = db1.getResult("SELECT * FROM supplier");
			printResultSupplier(rs4);

			System.out
					.println(db1
							.updateCommand("INSERT INTO supplier(SUPPLIER_ID,SUPPLIER_NAME) VALUES(125, 'p Journey')"));
			System.out
					.println(db1
							.updateCommand("INSERT INTO supplier(SUPPLIER_ID,SUPPLIER_NAME) VALUES(130, 'p Spandu Ballet')"));

			ResultSet rs5 = db1.getResult("SELECT * FROM supplier");
			printResultSupplier(rs5);

			db1.close();
		} catch (SQLException ex) {
			ex.printStackTrace(System.out);
		}
	}
}

